This invention is related in general to digital processing systems and more specifically to a system for monitoring and improving the performance of software such as a database system.
It is important, often vitally so, to improve the performance of software programs. This is particularly true in applications where large and complex software systems experience decreased execution speed, or other inefficiencies, due to lack of resources. Such resources are typically central processing unit (CPU) time, or cycles, and memory space, or other storage capacity.
For example, database systems need to provide fast, accurate, and efficient searching, presentation and other manipulation, of data in databases. An example of a popular database system is the xe2x80x9cDB2xe2x80x9d database engine. A common query language for a DB2 system is SQL. Such a system is a relational database that allows users to make queries of the database using relational operators such as AND, OR, NOT, etc. Different types of records can be defined and associated with other records. The records can be sorted, or otherwise processed, intermittently. Also, a DB2 system can be accessed by many simultaneous human users, or automated software processes, either in a parallel or timeshared fashion.
Many levels of complexity exist in database systems. Much of this complexity is due to the sheer size of the database, and to relationships between records and other data structures in the database. The time needed to execute a query, and return the desired results, is due to many factors that include the type of query, the design of the database, the allocation and type of resources, the number of users in a system, the design of the query engine, the design of the query; and other factors. For example, a system typically has a limited amount of random-access memory (RAM). Such memory is of a high-speed and should, ideally, be used to store items that are currently needed by the CPU, which will be needed in the near future, or which will be needed relatively often. However, allocating the right amount of RAM to a database system (e.g., in the form of xe2x80x9cbuffer poolsxe2x80x9d) is a complex problem whose solution may change frequently as the processing conditions of the overall system change. Although the problem is complex, it is important to be able to resolve the problem since allocation of resources in the form of buffer pools can greatly improve the performance of a database system.
Another important factor in analyzing the performance of database systems is execution time. Software executing in a database system, such as DB2, can include many large applications. Each application, in turn, can include packages, sections and other organizations of SQL text, or statements. It is important for a database administrator to be able to detect which portions of SQL text are consuming an undue amount of resources, such as CPU time, as this might indicate an improperly, or inefficiently, programmed query or database task. There are other factors of importance to database performance such as determining when a task, or query, will end based on system costs, estimated cardinality and delta delivery rates of data.
Thus, it is desirable to provide an effective performance monitoring and management system.
The present invention allows precise determination of resource use within the database system. One embodiment of the invention allows a user to investigate resource usage by SQL statement. The invention is capable of efficiently identifying unique dynamic SQL statements (i.e., Equalized I.D.).
The invention provides a global view which efficiently presents to a user, or database administrator, resource costs irrespective of other often-provided misleading characteristics such as Connection, Program, AuthID, ApplID. The invention can provide predictions for when a query, or other database task, will end based on selectable parameters such as optimizer cost, estimated cardinality, and delta delivery rates of data. In a preferred embodiment, a user interface is provided whereby a user can create or select event monitors to acquire statistical data on a database task. The results of activating the event monitor are presented to the user such that the user can progressively examine portions of an application, package, section and even individual statements. The user interface allows a user to efficiently and precisely identify processing shortcomings so that the performance of a database task can be improved. The interface also allows a user to sort, or otherwise organize, the statistical data so that analysis of the data is improved.
In one embodiment, the invention provides a method for analyzing the performance of an application in a database system. The method includes steps of accepting first signals from the user input device to select the application; displaying at least a portion of the first-level statement groups; accepting second signals from the user input device to select a first-level statement group; displaying at least a portion of second-level statement groups associated with the first-level statement group; and displaying statistical data on statements within at least one of the second-level statement groups associated with the first-level statement group.